package com.example.crm.dao;

import com.example.crm.domain.Customer;
import com.example.crm.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CustomerDAO {
    public Customer findById(int id) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "select * from cst_customer where cust_id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        stmt.setInt(1, id);

        // 3. 执行层语句
        ResultSet rs = stmt.executeQuery();
        Customer c = null;
        if (rs.next()) {
            String name = rs.getString("cust_name");
            int userId = rs.getInt("cust_user_id");
            int createId = rs.getInt("cust_create_id");
            String source = rs.getString("cust_source");
            String industry = rs.getString("cust_industry");
            String level = rs.getString("cust_level");
            String linkman = rs.getString("cust_linkman");
            String phone = rs.getString("cust_phone");
            String mobile = rs.getString("cust_mobile");

            c = new Customer(id, name, userId, createId, source, industry, level, linkman, phone, mobile);

        }
        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return c;
    }




        public int insert(Customer c) throws SQLException {
       // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "insert into cst_customer values (null,?,?,?,?,?,?,?,?,?)";
        PreparedStatement stmt = conn.prepareStatement(sql);

        // (需要修改)
        stmt.setString(1, c.getName());
        stmt.setInt(2, c.getUserId());
        stmt.setInt(3, c.getCreateId());
        stmt.setString(4, c.getSource());
        stmt.setString(5, c.getIndustry());
        stmt.setString(6, c.getLevel());
        stmt.setString(7, c.getLinkman());
        stmt.setString(8, c.getPhone());
        stmt.setString(9, c.getMobile());

        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }

    // 查询所有
    public List<Customer> findAll() throws SQLException
    {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "select * from cst_customer";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        // 3. 执行层语句
        ResultSet rs = stmt.executeQuery();
        List<Customer> list = new ArrayList<>();
        while(rs.next())
        {
            int id = rs.getInt("cust_id");
            String name = rs.getString("cust_name");
            int userId= rs.getInt("cust_user_id");
            int createId=rs.getInt("cust_create_id");
            String source = rs.getString("cust_source");
            String industry = rs.getString("cust_industry");
            String level = rs.getString("cust_level");
            String linkman = rs.getString("cust_linkman");
            String phone = rs.getString("cust_phone");
            String mobile = rs.getString("cust_mobile");

            Customer c = new Customer(id,name,userId,createId,source,industry,level,linkman,phone,mobile);
            list.add(c);
        }

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return list;
    }

    public int deleteById(int id) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "delete from cst_customer where cust_id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        // (需要修改)
        stmt.setInt(1, id);

        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }


    public int update(Customer c) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "update cst_customer set "
                + "cust_name=?,"
                + "cust_source=?,"
                + "cust_level=?,"
                + "cust_linkman=?,"
                + "cust_phone=?,"
                + "cust_mobile=? where cust_id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        // (需要修改)
        stmt.setString(1, c.getName());
        stmt.setString(2, c.getSource());
        stmt.setString(3, c.getLevel());
        stmt.setString(4, c.getLinkman());
        stmt.setString(5, c.getPhone());
        stmt.setString(6, c.getMobile());

        stmt.setInt(7, c.getId());

        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }

}
